﻿using KZ.Express.ENT;
using KZ.Express.Resource;
using KZ.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace KZ.Express.Presenter
{
    public class CustomerExportPresenter : PresenterBase
    {
        public CustomerExportPresenter(IViewBase view)
            : base(view, new OrderENT())
        {

        }

        private void BorderAround(Excel.Range range)
        {
            Excel.Borders borders = range.Borders;
            borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

            Marshal.ReleaseComObject(borders);
        }

        public DataTable LoadOrder(DateTime from, DateTime to, int carrierID, int customerID)
        {
            try
            {
                SearchCriteria search = new SearchCriteria();
                search.Add("From", from);
                search.Add("To", to);
                search.Add("CarrierID", carrierID);
                search.Add("CustomerID", customerID);

                return _task.LoadDataTable(search);
            }
            catch (DataAccessException ex)
            {
                throw new BusinessLogicException(ex.Message);
            }
        }

        public void Export(string fileName, DataRowView carrierRow, DataRowView customerRow, DataTable dt, int rateCount, string template)
        {
            Excel.Application xlApp = null;
            Excel.Workbooks xlWorkBooks = null;
            Excel.Workbook xlWorkBook = null;
            Excel.Worksheet xlWorkSheet = null;

            try
            {
                int rowCount = dt.Rows.Count;

                _view.UpdateValueProgressBar(0);
                _view.ChangeProgressBarVisible(true);
                _view.ChangeProgressBarMaximum(rowCount);

                xlApp = new Excel.Application();
                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Open(template, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                xlWorkSheet = xlWorkBook.ActiveSheet as Excel.Worksheet;

                xlWorkSheet.Cells[5, 1] = "BẢNG KÊ PHÍ DỊCH VỤ CHUYỂN PHÁT NHANH";
                xlWorkSheet.Cells[8, 1] = "TÊN KHÁCH HÀNG: " + Utility.StringParse(customerRow["PartnerName"]);
                xlWorkSheet.Cells[9, 1] = "ĐỊA CHỈ: " + Utility.StringParse(customerRow["Address"]);
                xlWorkSheet.Cells[10, 1] = "MST: " + Utility.StringParse(customerRow["TaxCode"]);
                xlWorkSheet.Cells[11, 1] = "ĐT: " + Utility.StringParse(customerRow["Phone"]);
                xlWorkSheet.Cells[11, 5] = "Email: " + Utility.StringParse(customerRow["Email"]);
                xlWorkSheet.Cells[12, 1] = "Công nợ T" + DateTime.Now.Month + "/" + DateTime.Now.Year;

                int rowIndex = 15;
                decimal totalFuel = 0, totalVAT = 0, total = 0;
                for (int i = 0; i < rowCount; i++)
                {
                    xlWorkSheet.Cells[rowIndex, 1] = i + 1;
                    xlWorkSheet.Cells[rowIndex, 2] = dt.Rows[i]["OrderDate"];
                    xlWorkSheet.Cells[rowIndex, 3] = dt.Rows[i]["OrderNumber"];
                    xlWorkSheet.Cells[rowIndex, 4] = dt.Rows[i]["Type"];
                    xlWorkSheet.Cells[rowIndex, 5] = Utility.StringParse(dt.Rows[i]["CountryName"]).ToUpper();
                    xlWorkSheet.Cells[rowIndex, 6] = dt.Rows[i]["Weight"];
                    xlWorkSheet.Cells[rowIndex, 7] = dt.Rows[i]["ListPrice"];

                    decimal listPrice = Utility.DecimalParse(dt.Rows[i]["ListPrice"]);
                    decimal vndListOptional = Utility.DecimalParse(dt.Rows[i]["VNDListOptional"]);
                    decimal fuel = listPrice * Utility.DecimalParse(dt.Rows[i]["Fuel"]) / 100;
                    decimal vat = (listPrice + fuel) * Utility.DecimalParse(dt.Rows[i]["vat"]) / 100;

                    totalFuel += fuel;
                    totalVAT += vat;
                    total += listPrice + vndListOptional;

                    rowIndex++;

                    _view.PerformStepProgressBar(1);
                }

                BorderAround(xlWorkSheet.Range[xlWorkSheet.Cells[15, 1], xlWorkSheet.Cells[rowCount + 14, 7]]);

                //// set font size order content
                xlWorkSheet.Range[xlWorkSheet.Cells[15, 1], xlWorkSheet.Cells[rowCount + 14, 7]].Font.Size = 12;
                xlWorkSheet.Range[xlWorkSheet.Cells[15, 1], xlWorkSheet.Cells[rowCount + 14, 7]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                //// format datetime
                xlWorkSheet.Range[xlWorkSheet.Cells[15, 2], xlWorkSheet.Cells[rowCount + 14, 2]].NumberFormat = "dd-mmm";

                //// charge
                xlWorkSheet.Range[xlWorkSheet.Cells[15, 7], xlWorkSheet.Cells[rowCount + 14, 7]].NumberFormat = "#,##0.00";

                //// total for shipment
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 1, 1], xlWorkSheet.Cells[rowIndex + 1, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 1, 7], xlWorkSheet.Cells[rowIndex + 1, 7]].NumberFormat = "#,##0.00";
                xlWorkSheet.Cells[rowIndex + 1, 1] = "Cộng tiền hàng";
                xlWorkSheet.Cells[rowIndex + 1, 7] = total;

                //// fuel charge
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 2, 1], xlWorkSheet.Cells[rowIndex + 2, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 2, 7], xlWorkSheet.Cells[rowIndex + 2, 7]].NumberFormat = "#,##0.00";
                xlWorkSheet.Cells[rowIndex + 2, 1] = "Phụ phí xăng dầu";
                xlWorkSheet.Cells[rowIndex + 2, 7] = totalFuel;

                //// vat
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 3, 1], xlWorkSheet.Cells[rowIndex + 3, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 3, 7], xlWorkSheet.Cells[rowIndex + 3, 7]].NumberFormat = "#,##0.00";
                xlWorkSheet.Cells[rowIndex + 3, 1] = "Tiền thuế GTGT";
                xlWorkSheet.Cells[rowIndex + 3, 7] = totalVAT;

                //// grand total
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 4, 1], xlWorkSheet.Cells[rowIndex + 4, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 4, 7], xlWorkSheet.Cells[rowIndex + 4, 7]].NumberFormat = "#,##0.00";
                xlWorkSheet.Cells[rowIndex + 4, 1] = "Tổng cộng tiền hàng";
                xlWorkSheet.Cells[rowIndex + 4, 7].Font.Bold = true;
                xlWorkSheet.Cells[rowIndex + 4, 7] = total + totalFuel + totalVAT;

                //// rate
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 5, 1], xlWorkSheet.Cells[rowIndex + 5, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 5, 7], xlWorkSheet.Cells[rowIndex + 5, 7]].NumberFormat = "#,##0";
                xlWorkSheet.Cells[rowIndex + 5, 1] = "Tỉ giá USD";
                decimal rate = 0;
                if (rateCount == 1)
                {
                    rate = Utility.DecimalParse(dt.Rows[0]["RATE"]);
                    xlWorkSheet.Cells[rowIndex + 5, 7] = rate;
                }

                //// grand total vnd
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 6, 1], xlWorkSheet.Cells[rowIndex + 6, 6]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 6, 7], xlWorkSheet.Cells[rowIndex + 6, 7]].NumberFormat = "#,##0";
                xlWorkSheet.Cells[rowIndex + 6, 1] = "Tổng cộng tiền thanh toán";
                xlWorkSheet.Cells[rowIndex + 6, 7].Font.Bold = true;
                xlWorkSheet.Cells[rowIndex + 6, 7] = (total + totalFuel + totalVAT) * rate;

                //// set font size footer
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex + 1, 1], xlWorkSheet.Cells[rowIndex + 6, 7]].Font.Size = 14;

                rowIndex += 8;
                using (StreamReader sr = new StreamReader(Path.Combine(Utility.AppPath, @"Template\Customer Export Footer.txt")))
                {
                    while (!sr.EndOfStream)
                    {
                        xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 1], xlWorkSheet.Cells[rowIndex, 7]].Merge();
                        xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 1], xlWorkSheet.Cells[rowIndex, 7]].Font.Size = 12;
                        xlWorkSheet.Cells[rowIndex, 1] = sr.ReadLine();
                        rowIndex++;
                    }
                }

                //// save the application
                xlApp.DisplayAlerts = false;
                xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                _view.UpdateValueProgressBar(rowCount);
            }
            catch (Exception ex)
            {
                Log.LogEx(Message.BLM_ERR_GENERAL.ToString(), Log.LogType.Error, ex);
            }
            finally
            {
                if (xlApp != null)
                {
                    xlApp.Quit();
                    //// these are some cleanup calls
                    System.Threading.Thread.Sleep(1000);
                }

                if (xlWorkSheet != null) Marshal.ReleaseComObject(xlWorkSheet);
                if (xlWorkBook != null) Marshal.ReleaseComObject(xlWorkBook);
                if (xlWorkBooks != null) Marshal.ReleaseComObject(xlWorkBooks);
                if (xlApp != null) Marshal.ReleaseComObject(xlApp);

                _view.ChangeProgressBarVisible(false);
            }
        }
    }
}
